{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2717: DtypeWarning: Columns (28,30,47,58,59,60,61,62,71,72,73,74,75,83,91,98,100,101,104,109,113,114,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2717: DtypeWarning: Columns (2,20,21,28,47,58,72,83,91,98,101,104,106,120,121,124,125,127,130,131) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "dt1=pd.read_csv(r'C:\\Users\\xy\\Desktop\\export_2018-01-01_2018-03-31_survey_9.csv',encoding='gbk')\n",
    "dt2=pd.read_csv(r'C:\\Users\\xy\\Desktop\\export_2018-04-01_2018-06-30_survey_9.csv',encoding='gbk')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dt=pd.concat([dt1,dt2],ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def clean_data(x):\n",
    "    for i in range(len(x)):\n",
    "        if re.match('[\\d]',str(x[i])) is not None:\n",
    "            x[i]=int(re.match(r'[\\d]',str(x[i])).group())\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dt.ix[:,25:]=dt.ix[:,25:].apply(clean_data,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dt.replace('CAPSE',np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dt=dt.apply(pd.to_numeric,errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dt['航司']=list(map(lambda x:x[:2],dt['航班号']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dt['出发和']=dt[['机场大巴服务','停车场服务','机场指示牌','机场环境卫生','机场设施设备','机场信息通报','机场卫生间',\n",
    "              '机场WIFI','机场建设文化特色','机场柜台值机满意度','机场自助值机满意度','安检满意度','工作人员服务',\n",
    "              '机场餐饮种类满意度','机场商品种类','机场餐饮价格','机场商品价格']].sum(1)\n",
    "dt['航司和']=dt[['出发机场摆渡车服务','登机满意度','安全服务','空乘服务','机上广播','机上餐食','机上饮品','机上娱乐',\n",
    "              '客舱环境','座椅舒适度','机上卫生间','对行李保护','行李传输时间满意度（在行李转盘等待的时间）']].sum(1)\n",
    "dt['到达和']=dt[['行李推车满意度','对行李保护','行李传输时间满意度（在行李转盘等待的时间）','行李提取引导',\n",
    "              '机场卫生间满意度','机场大巴满意度','出租车满意度','交通满意度（地铁、其他）','停车场满意度']].sum(1)\n",
    "dt['出发项']=dt[['机场大巴服务','停车场服务','机场指示牌','机场环境卫生','机场设施设备','机场信息通报','机场卫生间',\n",
    "              '机场WIFI','机场建设文化特色','机场柜台值机满意度','机场自助值机满意度','安检满意度','工作人员服务',\n",
    "              '机场餐饮种类满意度','机场商品种类','机场餐饮价格','机场商品价格']].count(1)\n",
    "dt['航司项']=dt[['出发机场摆渡车服务','登机满意度','安全服务','空乘服务','机上广播','机上餐食','机上饮品','机上娱乐',\n",
    "              '客舱环境','座椅舒适度','机上卫生间','对行李保护','行李传输时间满意度（在行李转盘等待的时间）']].count(1)\n",
    "dt['到达项']=dt[['行李推车满意度','对行李保护','行李传输时间满意度（在行李转盘等待的时间）','行李提取引导',\n",
    "              '机场卫生间满意度','机场大巴满意度','出租车满意度','交通满意度（地铁、其他）','停车场满意度']].count(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pivot1=dt[['CAPSE用户ID','点评ID']].groupby('CAPSE用户ID').count()\n",
    "pivot2=dt[['CAPSE用户ID','航司']].groupby(['CAPSE用户ID','航司']).count().reset_index().groupby(['CAPSE用户ID']).count()\n",
    "pivot3=dt[['CAPSE用户ID','出发地编码']].groupby(['CAPSE用户ID','出发地编码']).count().reset_index().groupby(['CAPSE用户ID']).count()\n",
    "pivot4=dt[['CAPSE用户ID','目的地编码']].groupby(['CAPSE用户ID','目的地编码']).count().reset_index().groupby(['CAPSE用户ID']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pivot=pd.concat([pivot1,pivot2,pivot3,pivot4],axis=1)\n",
    "pivot.columns=['点评次数','航司计数','出发计数','到达计数']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1=dt[['CAPSE用户ID','总体满意度']].groupby('CAPSE用户ID').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2=dt[['CAPSE用户ID','出发和','航司和','到达和','出发项','航司项','到达项']].groupby('CAPSE用户ID').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2['出发平均']=df2['出发和']/df2['出发项']\n",
    "df2['航司平均']=df2['航司和']/df2['航司项']\n",
    "df2['到达平均']=df2['到达和']/df2['到达项']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df=pivot.join(df1.join(df2[['出发平均','航司平均','到达平均']]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_train=pd.read_excel(r'E:\\HL\\blacklist\\train.xlsx',index_col=\"用户ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#其他缺失值以UNKNOW代替\n",
    "df_train.fillna('UNKNOW',inplace=True)\n",
    "df.fillna('UNKNOW',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#进行属性选择及抽取数据\n",
    "x_train=df_train.drop([\"黑名单\"],axis=1)\n",
    "y_train=df_train['黑名单']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#类别特征向量化\n",
    "from sklearn.feature_extraction import DictVectorizer\n",
    "vec=DictVectorizer()\n",
    "x_train=vec.fit_transform(x_train.to_dict(orient='record'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x_test=vec.transform(df.to_dict(orient='record'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#使用随机森林分类器训练模型并输出性能\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "rfc=RandomForestClassifier(n_jobs=8)\n",
    "rfc.fit(x_train,y_train)\n",
    "rfc_y_pred=rfc.predict(x_test)\n",
    "rfc_y_prob=rfc.predict_proba(x_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['黑名单']=rfc_y_pred\n",
    "df['概率']=rfc_y_prob[:,1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_excel(r'C:\\Users\\xy\\Desktop\\blacklist.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
